package com.ekingstar.framework.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.impl.SQLQueryImpl;
import org.hibernate.transform.ResultTransformer;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateTemplate;

import com.ekingstar.framework.domain.PublicCode;
import com.ekingstar.framework.domain.User;
import com.ekingstar.framework.domain.User.Type;
import com.ekingstar.framework.service.CodeException;
import com.ekingstar.framework.service.CodeService;

public class CodeServiceImpl implements CodeService{
	
	private static Map codeMaps = new LinkedHashMap();
	private HibernateTemplate hibernateTemplate;
	public void setHibernateTemplate(HibernateTemplate hibernateTemplate) {
		this.hibernateTemplate = hibernateTemplate;
	}
/**
 * 代码表的code和name进行转换
 * @param tableName 代码表表名
 * @param fromColumnName 需要转换
 * @param toColumnName 转换成
 * @param value 需要转换的值
 * @return 代码表code或name
 */
	public String getCodeFromName(final String tableName,final String fromColumnName,
			final String toColumnName,final String value) throws CodeException {
			 Map codeNames = (Map)codeMaps.get(tableName);
			if (codeNames == null){
				codeNames =new LinkedHashMap();
				codeMaps.put(tableName, codeNames);
				final Map tcodeNames = codeNames;
				HibernateCallback hibernateCallback=new HibernateCallback(){
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						session.createSQLQuery("select name,code from "+tableName ).setResultTransformer(new ResultTransformer(){
							public List transformList(List collection) {
								// TODO Auto-generated method stub
								return null;
							}
							public Object transformTuple(Object[] tuple,
									String[] aliases) {
								// TODO Auto-generated method stub
								tcodeNames.put(tuple[1],tuple[0]);
								return null;
							}
							
						}).list();
						return null;
						
					}
				};
				hibernateTemplate.execute(hibernateCallback);
			}
			
			String val = (String)codeNames.get(value);
			if (val == null){
				return value;
			}
			return val;
		
			
			
		
	}
	
	public String getCodeFromNameIfExist(final String tableName,final String fromColumnName,
			final String toColumnName,final String value) throws CodeException {
			final List returnList=new ArrayList();
			HibernateCallback hibernateCallback=new HibernateCallback(){
				public Object doInHibernate(Session session)
						throws HibernateException, SQLException {
					String sql="select "+toColumnName+" from "+tableName+" where "+ fromColumnName+"='"+value+"'";
					Query query = session.createSQLQuery(sql);
					List list=((SQLQueryImpl)query).list();
					if(list.size()>0)
					{
						returnList.add(list.get(0));
						return returnList.get(0);		
					}
					else
					{
						return value;
					}
					
				}
			};
			hibernateTemplate.execute(hibernateCallback);
			if(returnList.size()>0)
			{
				return (String)returnList.get(0);
			}
			else
			{
				throw new  CodeException("代码不存在");
			}
		
	}
	/**
	 * 返回select控件的PublicCode的list集合
	 * @param tableName 代码表表名
	 * @param key code
	 * @param value name
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */
	public List<PublicCode> getPulbicList(final String tableName,final String key,
			final String value) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","---请选择---");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="select "+key+","+value+" from "+tableName +" order by "+value;
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	/**
	 * 返回select的PublicCode的list集合
	 * @param tableName 代码表表名
	 * @param key code
	 * @param value name
	 * @param param where 条件
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */
	public List<PublicCode> getPulbicList(final String tableName,final String key,
			final String value,final String param) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","---请选择---");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="select "+key+","+value+" from "+tableName +" where "+param+" order by "+value;
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	/**
	 * 返回combobox的PublicCode的list集合
	 * @param tableName 代码表表名
	 * @param key code
	 * @param value name
	 * @param param where 条件
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */
	public List<PublicCode> getPulbicListForComboBox(final String tableName,final String key,
			final String value,final String param) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="select "+key+","+value+" from "+tableName +" where "+param+" order by "+key;
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	
	public List<PublicCode> getPulbicListForMoreSelect(final String tableName,final String key,
			final String value,final String param) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="select "+key+","+value+" from "+tableName +" where "+param+" order by "+key;
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	
	/**
	 * 返回select控件的PublicCode的list集合
	 * @param sql 需要写成select key,value的形式
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */
	public List<PublicCode> getPulbicList(final String sql) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","---请选择---");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	/**
	 * 返回combobox控件的PublicCode的list集合
	 * @param tableName 代码表表名
	 * @param key code
	 * @param value name
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */
	public List<PublicCode> getPulbicListForComboBox(final String tableName,final String key,
			final String value) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String sql="select "+key+","+value+" from "+tableName +" order by "+key;
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	
	
	
	
	/**
	 * 返回combobox控件的PublicCode的list集合
	 * @param sql 需要写成select key,value的形式
	 * @return PublicCode的list集合
	 * @throws CodeException
	 */

	public List<PublicCode> getPulbicListForComboBox(final String sql) throws CodeException {
		final List<PublicCode> returnList=new ArrayList();
		PublicCode publicCode=new PublicCode("","");
		returnList.add(publicCode);
		HibernateCallback hibernateCallback=new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createSQLQuery(sql);
				List list=((SQLQueryImpl)query).list();
				if(list.size()>0)
				{
					for(int i=0;i<list.size();i++)
					{
						Object[] code=(Object [])list.get(i);
						PublicCode publicCode=new PublicCode(code[0].toString(),code[1].toString());
						returnList.add(publicCode);
					}	
				}
				return returnList;	
			}
		};
		hibernateTemplate.execute(hibernateCallback);
		return returnList;
	}
	public Map getNameCodes(final String table) {
		// TODO Auto-generated method stub
		final Map nameCodes = new LinkedHashMap();
		hibernateTemplate.execute(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				session.createSQLQuery("select name,code from "+table ).setResultTransformer(new ResultTransformer(){
					public List transformList(List collection) {
						// TODO Auto-generated method stub
						return null;
					}
					public Object transformTuple(Object[] tuple,
							String[] aliases) {
						// TODO Auto-generated method stub
						nameCodes.put(tuple[0],tuple[1]);
						return null;
					}
					
				}).list();
				return null;
			}
			
		});
		return nameCodes;
	}
	//
	public Map getCodeNames(final String table) {
		// TODO Auto-generated method stub
		final Map nameCodes = new LinkedHashMap();
		hibernateTemplate.execute(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				session.createSQLQuery("select code,name from "+table ).setResultTransformer(new ResultTransformer(){
					public List transformList(List collection) {
						// TODO Auto-generated method stub
						return null;
					}
					public Object transformTuple(Object[] tuple,
							String[] aliases) {
						// TODO Auto-generated method stub
						nameCodes.put(tuple[0],tuple[1]);
						return null;
					}
					
				}).list();
				return null;
			}
			
		});
		return nameCodes;
	}
	public Map getCodeAndName(final String table,final String column1, final String column2) {
		// TODO Auto-generated method stub
		final Map nameCodes = new LinkedHashMap();
		hibernateTemplate.execute(new HibernateCallback(){
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				String str = "select "+column1+","+column2+ " from " + table;
				session.createSQLQuery(str).setResultTransformer(new ResultTransformer(){
					public List transformList(List collection) {
						return null;
					}
					public Object transformTuple(Object[] tuple,
							String[] aliases) {
						nameCodes.put(tuple[0],tuple[1]);
						return null;
					}
				}).list();
				return null;
			}
			
		});
		return nameCodes;
	}
	/**
	 * 取用户类比
	 */
	public List<PublicCode> getUserType() {
		final List<PublicCode> returnList=new ArrayList();
		returnList.add(new PublicCode("",""));
		Type[] type= User.Type.values();
		for(Type usertype:type)
		{
			PublicCode publicCode=new PublicCode(usertype.name(),usertype.toString());
			returnList.add(publicCode);
		}
		return returnList;
	}
}
